﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_NodeRevision]') AND type in (N'U')) 
AND NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'medianamik_NodeRevision' AND COLUMN_NAME = 'CultureName')
ALTER TABLE [dbo].[medianamik_NodeRevision]
ADD CultureName varchar(10) NULL

IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetLastNodeRevisions]') AND type in (N'P', N'PC'))
BEGIN
EXEC('CREATE PROCEDURE [dbo].[medianamik_GetLastNodeRevisions] 
	-- Add the parameters for the stored procedure here
	@NodeId UNIQUEIDENTIFIER , 
	@RevisionMax DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @CurrentDate DATETIME
	
	SET @CurrentDate = GetDate()
	IF @RevisionMax IS NULL
		SET @RevisionMax = GetDate()
	
	SELECT nr.* FROM medianamik_noderevision nr
	INNER JOIN (SELECT NodeId, Max(ISNULL(PublishedOn, @CurrentDate)) as PublishedOn, ISNULL(CultureName, '''') as CultureName from medianamik_noderevision
	Where NodeId = @NodeId AND ISNULL(PublishedOn, @CurrentDate) <= @RevisionMax
	GROUP BY NodeId, CultureName) as nrjoin on nr.NodeId = nrjoin.NodeId AND ISNULL(nr.CultureName, '''') = nrjoin.CultureName and ISNULL(nr.PublishedOn, @CurrentDate) = nrjoin.PublishedOn
END')
GRANT EXECUTE ON [dbo].[medianamik_GetLastNodeRevisions] TO [Medianamik_DataAccess]
END
